import pymysql


def get_conn():
    # 建立连接
    host = "localhost"
    users = "root"
    password = "wql000218"
    db = "cov"
    charset = "utf8"
    conn = pymysql.connect(host=host, user=users, password=password, db=db, charset=charset)
    # c创建游标A
    cursor = conn.cursor()
    return conn, cursor


def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()


def query(sql, *args):
    """

    :param sql:
    :param args:
    :return:
    """
    conn, cursor = get_conn()
    cursor.execute(sql, args)
    res = cursor.fetchall()
    close_conn(conn, cursor)
    return res


def get_yq_data():
    sql = "select confirmedRelative, asymptomaticRelative, " \
          "overseasInputRelative, nativeRelative, curConfirm," \
          " curLocalConfirm, curOverseasInput, asymptomatic from c_now"
    res = query(sql)
    return res[0]


def get_confirmedRelative_data():
    sql1 = "select province,confirmedRelative from c_province"
    res1 = query(sql1)
    return res1


def get_curConfirm_data():
    sql1 = "select province,curConfirm from c_province"
    res1 = query(sql1)
    return res1


def get_confirmed_data():
    sql1 = "select province,confirmed from c_province"
    res1 = query(sql1)
    return res1


def get_echart2_data():
    sql = "select province,city,confirmedRelative from c_city " \
          "where updatetime=(select updatetime from c_city order by updatetime desc limit 1) " \
          "and city not in('境外输入，待确认') " \
          "order by confirmedRelative desc limit 10"
    res_echart2 = query(sql)
    return res_echart2


def get_echart3_data():
    sql = "select upadatetime,confirmedRelative,asymptomaticRelative,curedRelative,diedRelative " \
          "from c_history order by upadatetime desc limit 14"
    res_echarts = query(sql)
    return res_echarts


def get_echart4_data():
    sql1 = "select overseasInputRelative from c_now"
    sql2 = "select nativeRelative from c_now"
    res1 = query(sql1)
    res2 = query(sql2)
    return res1, res2


def get_echart5_data():
    sql1 = "select city,count(*) from c_highdangerousareas group by city "
    sql2 = "select city,count(*) from c_middleDangerousAreas group by city "
    res1 = query(sql1)
    res2 = query(sql2)

    return res1, res2


def get_C(s):
    sql1 = "select count(*) from c_middleDangerousAreas where city = '%s'" % s
    sql2 = "select count(*) from c_highdangerousareas where city = '%s'" % s
    res1 = query(sql1)
    res2 = query(sql2)
    return res1, res2
